﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using Oracle.ManagedDataAccess.Client;

namespace bmapi.Models.DataEntity
{

    public class SQLCommand
    {
        public static readonly string connstr = ConfigurationManager.ConnectionStrings["connectStr"].ToString();
        #region  sql命令  参数
        public SqlCommand cmd = new SqlCommand();
        
        public void Add(string parameterName, object value)
        {
            cmd.Parameters.Add(new SqlParameter(parameterName, value));
        }
        public SQLCommand(string sqlcmd)
        {
            cmd = new SqlCommand(sqlcmd);
        }
        public SQLCommand(string sqlcmd, string parameterName, object value)
        {
            cmd.CommandText = sqlcmd;
            cmd.Parameters.Add(new SqlParameter(parameterName, value));
        }
        public SQLCommand() { }
        #endregion 

        /// <summary>
        /// 非查询
        /// </summary>
        /// <returns></returns>
        public int ExecuteNonQuery()
        {
            using (SqlConnection conn = new SqlConnection(connstr))
            {
                conn.Open();
                cmd.Connection = conn;
                int result = cmd.ExecuteNonQuery();
                return result;
            }
        }

        #region  查询 
        /// <summary>
        /// 多个值查询
        /// </summary>
        /// <returns>dt</returns>
        public DataTable ExecuteScalars()
        {
            DataSet set = new DataSet();
            using (SqlConnection conn = new SqlConnection(connstr))
            {
                conn.Open();
                cmd.Connection = conn;
                using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                {
                    //adapter.SelectCommand.Connection = conn;
                    adapter.Fill(set);
                }
                DataTable dt = set.Tables[0];
                if (dt.Rows.Count > 0)
                    return dt;
                else
                    return null;
            }
        }
        /// <summary>
        /// 返回一行
        /// </summary>
        /// <returns></returns>
        public DataRow ExecuteOneRow()
        {
            DataSet set = new DataSet();
            using (SqlConnection conn = new SqlConnection(connstr))
            {
                conn.Open();
                cmd.Connection = conn;
                using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                {
                    adapter.Fill(set);
                }
                DataTable dt = set.Tables[0];
                if (dt.Rows.Count > 0)
                    return dt.Rows[0];
                else
                    return null;
            }
        }
        /// <summary>
        /// 返回一个值 
        /// </summary>
        /// <returns></returns>
        public string ExecuteScalar()
        {
            using (SqlConnection conn = new SqlConnection(connstr))
            {
                conn.Open();
                cmd.Connection = conn;
                Object obj = cmd.ExecuteScalar();
                cmd.Parameters.Clear();
                if (obj != null)
                {
                    return obj.ToString();
                }
                else
                {
                    return null;
                }

            }
        }
        #endregion

        #region  执行事务 
        /// <summary>  
        /// 执行事务，返回0或者1  
        /// </summary>  
        /// <param name="Sqlstr">sql语句</param>  
        /// <returns></returns>  
        public static int ExecTran(List<SQLCommand> sqlcmds)
        {
            int res = 0;
            SqlConnection conn = new SqlConnection(connstr);
            conn.Open();
            // 先实例SqlTransaction类，使用这个事务使用的是con 这个连接，使用BeginTransaction这个方法来开始执行这个事务
            SqlTransaction tran = conn.BeginTransaction();
            try
            {
                for (int i = 0; i < sqlcmds.Count; i++)
                {
                    sqlcmds[i].cmd.Connection = conn;
                    sqlcmds[i].cmd.Transaction = tran;
                    res = sqlcmds[i].cmd.ExecuteNonQuery();
                }
                tran.Commit();
                return 1;
            }
            catch (Exception ex)
            {
                tran.Rollback();
                return 0;
            }
            finally
            {
                conn.Close();
                tran.Dispose();
            }
        }
        #endregion 

        #region 获取分页的数据
        /// <summary>
        /// 获取分页的数据
        /// </summary>
        /// <param name="TblName">数据表名</param>
        /// <param name="Fields">要读取的字段</param>
        /// <param name="OrderField">排序字段 也可后接升降序 例：Time desc</param>
        /// <param name="SqlWhere">查询条件</param>
        /// <param name="PageSize">每页显示多少条数据</param>
        /// <param name="pageIndex">当前页码</param>
        /// <param name="TotalPage">返回值，共有多少页</param>
        /// <param name="TotalRecord">返回值，总有多少条记录</param>
        /// <returns></returns>
        public static DataTable GetDataListByPage(string TblName, string Fields, string OrderField, string SqlWhere, int PageSize, int pageIndex, out int TotalPage, out int TotalRecord)
        {

            SqlConnection conn = new SqlConnection(connstr);
            SqlCommand cmd = new SqlCommand("proc_GetListByPage", conn);
            #region
            cmd.Parameters.Add(new SqlParameter("@TableName", SqlDbType.NVarChar, 100)).Value = TblName;
            cmd.Parameters.Add(new SqlParameter("@Fields", SqlDbType.NVarChar, 1000)).Value = Fields;
            cmd.Parameters.Add(new SqlParameter("@OrderField", SqlDbType.NVarChar, 1000)).Value = OrderField;
            cmd.Parameters.Add(new SqlParameter("@sqlWhere", SqlDbType.NVarChar, 1000)).Value = SqlWhere;
            cmd.Parameters.Add(new SqlParameter("@pageSize", SqlDbType.Int)).Value = PageSize;
            cmd.Parameters.Add(new SqlParameter("@pageIndex", SqlDbType.Int)).Value = pageIndex;
            cmd.Parameters.Add(new SqlParameter("@TotalPage", SqlDbType.Int));

            cmd.Parameters["@TotalPage"].Direction = ParameterDirection.Output;//获得out出来的参数值

            cmd.Parameters.Add(new SqlParameter("@totalRecord", SqlDbType.Int));
            cmd.Parameters["@totalRecord"].Direction = ParameterDirection.Output;
            #endregion

            cmd.CommandType = CommandType.StoredProcedure;
            SqlDataAdapter dataAdapter = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();
            dataAdapter.Fill(ds);

            TotalPage = (int)cmd.Parameters["@TotalPage"].Value;
            TotalRecord = (int)cmd.Parameters["@totalRecord"].Value;
            conn.Close();
            conn.Dispose();
            cmd.Dispose();

            return ds.Tables[0];
        }
        #endregion

    }
    /// <summary>
    /// ORACLE数据库
    /// </summary>
    public class OrcCommand
    {
        public string Cmd { get; set; }
        public OracleParameter[] Paras { get; set; }

        public OrcCommand(string sqlcmd)
        {
            Cmd = sqlcmd;
        }
        public OrcCommand(string sqlcmd, params OracleParameter[] para)
        {
            Cmd = sqlcmd;
            Paras = para;
        }

        public OrcCommand() { }
        public void Add(params OracleParameter[] para)
        {
            Paras = para;
        }
    }
}